/* @(#)GSOrderDeptInfoDAO.java    
 *
 * Project: GS
 *
 * Modify Information:
 * =============================================================================
 *   Author       Date       Description
 *   ------------ ---------- ---------------------------------------------------
 *   Jenny Li     2006-2-14    新增
 *   owen         2006-2-16    
 *   Jenny Li     2006-3-14  新增方法queryOrderDeptAddrByOper
 *   Jenny Li     2006-3-16  新增方法queryOrderDeptInfoOfOperOrg，queryOrderDeptInfoOfOperSubOrg，queryAllOrderDeptInfoOfSubOrg
 *   Jenny Li     2006-3-18  添加方法queryOrderDeptInfoOfBusOperOrg
 *   Jenny Li     2006-3-24  添加方法queryOrderDeptInfoOfOrg
 * Copyright Notice:
 * =============================================================================
 *    Copyright (c) 2001-2006 Beijing HiTRUST Technology Co., Ltd. 1808 Room, 
 *        Science & Technology Building, No. 9 South Street, Zhong Guan Cun, 
 *        Haidian District, Beijing ,100081, China All rights reserved.
 *
 *    This software is the confidential and proprietary information of
 *        Beijing HiTRUST Technology Co., Ltd. ("Confidential Information").
 *        You shall not disclose such Confidential Information and shall use it
 *        only in accordance with the terms of the license agreement you entered
 *        into with HiTRUST.
 *
 * Warning:
 * =============================================================================
 * 
 */
package com.hitrust.gs.dao;

import java.sql.Connection;
import java.util.ArrayList;

import com.hitrust.gs.bean.GSOrderDeptInfoBean;
import com.hitrust.gs.bean.GSOrganizationBean;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.lib.db.GSBaseDAO;
import com.hitrust.gs.util.exception.GSException;
import com.hitrust.gs.vo.GSOrderDeptInfoVO;
import com.hitrust.gs.vo.GSOrganizationVO;

/**
 * 
 */
public class GSOrderDeptInfoDAO extends GSBaseDAO {
    /**
     * 查询所有订货单位
     * @return
     * @throws GSException
     */
    public ArrayList queryAllOrderDept() throws GSException{
        ArrayList tList = new ArrayList();
        GSOrganizationVO tGSOrgVO = new GSOrganizationVO();
        Object[] tObj = {};
        String tSql = "select distinct a.GS_ORG_ID,b.GS_ORG_NAME from GS_ORDER_DEPT_INFO a, GS_ORGANIZATION b where a.GS_ORG_ID=b.GS_ORG_ID";
        try{
            tList = this.executeQuery(tGSOrgVO,tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tList;
    }
    /**
     * 查询操作员可操作的订货单位    
     * @param aOperId           操作员编码
     * @return
     * @throws GSException
     */
    public ArrayList queryOrderDeptsByOper(String aOperId) throws GSException{
        ArrayList tList = new ArrayList();
        GSOrganizationVO tGSOrgVO = new GSOrganizationVO();
        Object[] tObj = {aOperId};
        String tSql = "select distinct a.GS_ORG_ID,c.GS_ORG_NAME from GS_ORDER_DEPT_INFO a,GS_OPERATOR b,GS_ORGANIZATION c " + 
                      "where a.GS_ORG_ID = c.GS_ORG_ID " +
                      "and (c.GS_ORG_ID = b.GS_OPER_ORG_ID or (b.GS_OPER_ORG_ID=c.GS_UPPERORG_ID and c.GS_ORG_ID = a.GS_ORG_ID)) " +
                      "and b.GS_OPER_ID=?";
        try{
            tList = this.executeQuery(tGSOrgVO,tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tList;
    }
    
    /**
     * 通过机构编号模糊查询所有订货单位常用联系方式
     * String aOrgId
     * @return ArrayList
     * @throws GSException
     */
    public ArrayList queryOrderDeptInfo(String aOrgId,String aAddr) throws GSException {
        String tSql = "select * from GS_ORDER_DEPT_INFO where GS_ORG_ID like '%" + aOrgId+ "%' " +
                "and GS_DELIVER_GOODS_ADDR like '%" + aAddr+ "%' " +
                "and GS_ORG_ID in (select GS_ORG_ID from GS_ORGANIZATION) " +
                "order by GS_ORG_ID";
        ArrayList tArrayList = null;
        Object[] tObject = {};
        try {
            tArrayList = executeQuery(new GSOrderDeptInfoBean(), tSql, tObject);
        } catch (Exception ex) {
            GSLogWriter.error(this, ex.toString());
            throw new GSException(ex.getMessage());
        }
        return tArrayList;
     }
    
    /**
     * 查询一个机构编号订货单位常用联系方式
     * String aOrgId
     * @return ArrayList
     * @throws GSException
     */
    public ArrayList getOrderDeptInfo(String aOrgId) throws GSException {
        String tSql = "select * from GS_ORDER_DEPT_INFO where GS_ORG_ID =?";
        ArrayList tArrayList = null;
        Object[] tObject = {aOrgId};
        try {
            tArrayList = executeQuery(new GSOrderDeptInfoBean(), tSql, tObject);
        } catch (Exception ex) {
            GSLogWriter.error(this, ex.toString());
            throw new GSException(ex.getMessage());
        }
        return tArrayList;
     }
    /**
     * 查询一个机构编号订货单位常用联系方式
     * String aOrgId
     * @return ArrayList
     * @throws GSException
     */
    public ArrayList queryOrderDeptInfoByOrgId(Connection aConn,String aOrgId) throws GSException {
        String tSql = "select * from GS_ORDER_DEPT_INFO where GS_ORG_ID =? order by GS_ORG_ID";
        ArrayList tArrayList = null;
        Object[] tObject = {aOrgId};
        try {
            tArrayList = executeQuery(aConn,new GSOrderDeptInfoBean(), tSql, tObject);
        } catch (Exception ex) {
            GSLogWriter.error(this, ex.toString());
            throw new GSException(ex.getMessage());
        }
        return tArrayList;
     }
    /**
     * 查询运营公司级操作员所在组织及订货单位信息
     * @param aConn                 DB连接
     * @param aOrgId                组织编码
     * @return
     * @throws GSException
     */
    public ArrayList queryOrderDeptInfoOfOrg(Connection aConn,String aOrgId) throws GSException{
        ArrayList tList = new ArrayList();
        String tSql = "select A.GS_ORG_ID as P1,A.GS_ORG_NAME as P2,B.GS_DELIVER_GOODS_ADDR_ID as P3,B.GS_DELIVER_GOODS_ADDR as P4" +
            " from GS_Organization A,GS_ORDER_DEPT_INFO B where A.GS_ORG_ID = ? and A.GS_ORG_ID = B.GS_ORG_ID " +
            " and SUBSTR(A.GS_ORG_ID,0,2)<>'00' order by A.GS_ORG_NAME";
        Object[] tObj = {aOrgId};
        try{
            tList = this.executeQuery(aConn,new GSOrderDeptInfoVO(),tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tList;
    }
        
    /**
     * 订货单位编号是否存在
     * @param orgId
     * @return
     * @throws GSException
     */
    public boolean isExist(String orgId) throws GSException{
        String tSql = "select * from GS_ORDER_DEPT_INFO where GS_Org_ID = ?";
        boolean tFlag = false;
        Object[] tObj = {orgId};
        try{
            ArrayList tList = this.executeQuery(new GSOrderDeptInfoBean(),tSql,tObj);
            if(tList.size() > 0){
                tFlag = true;
            }
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tFlag;
    }
    
    /**
     * 读取订货单位的常用联系方式
     * @param String aOrgId  订货单位编号
     * @param String aAddrId 送货地址编号
     * @return GSOrderDeptInfoBean
     * @throws GSException
     */
    public GSOrderDeptInfoBean queryOrderDeptInfoOne(String aOrgId,String aAddrId) throws GSException {
        String tSql = "select * from GS_ORDER_DEPT_INFO where GS_ORG_ID = ? and GS_DELIVER_GOODS_ADDR_ID = ?";
        ArrayList tList = null;
        GSOrderDeptInfoBean tGSOrderDeptInfoBean = null;
        Object[] tObj = {aOrgId,aAddrId};
        try{
            tList = this.executeQuery(new GSOrderDeptInfoBean(),tSql,tObj);
            if(tList.size() == 1){
                tGSOrderDeptInfoBean = (GSOrderDeptInfoBean)tList.get(0);
            }else{
                throw new GSException(GSException.E20031,GSException.E20031_MSG);
            }
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        
        return tGSOrderDeptInfoBean;
    }
    
    /**
     * 在订货单位常用联系方式表GS_ORDER_DEPT_INFO中新增一笔记录
     * 
     * @param aMerTempBean
     * 
     * return boolean
     * 
     * @throws CUPSException
     */
    public boolean add(GSOrderDeptInfoBean aGSOrderDeptInfoBean) throws GSException {

        String tSql="INSERT INTO GS_ORDER_DEPT_INFO (GS_ORG_ID," +
                "GS_DELIVER_GOODS_ADDR_ID,GS_DELIVER_GOODS_ADDR,GS_ORDER_CONTACT_NAME," +
                "GS_ORDER_CONTACT_PHONE) VALUES(?,?,?,?,?)";

        Object[] tObject = new Object[5] ;
          tObject[0]=aGSOrderDeptInfoBean.getGs_org_id();
          tObject[1]=aGSOrderDeptInfoBean.getGs_deliver_goods_addr_id();
          tObject[2]=aGSOrderDeptInfoBean.getGs_deliver_goods_addr();
          tObject[3]=aGSOrderDeptInfoBean.getGs_order_contact_name();
          tObject[4]=aGSOrderDeptInfoBean.getGs_order_contact_phone();
         
          
        boolean tResult = false;
        try {
            tResult = executeUpdate(tSql, tObject);

        } catch (Exception ex) {
            GSLogWriter.error(this, ex.toString());
            throw new GSException(ex.getMessage());
        }

        return tResult;
    }
    
    /**
     * 在订货单位常用联系方式表GS_ORDER_DEPT_INFO中更新一笔记录
     * 
     * @param MerchantTempBean
     * @throws CUPSException]
     *             return boolean
     */
    public boolean modify(GSOrderDeptInfoBean aGSOrderDeptInfoBean) throws GSException {

        String tSql = "UPDATE GS_ORDER_DEPT_INFO SET GS_DELIVER_GOODS_ADDR = ?,GS_ORDER_CONTACT_NAME=?," +
                "GS_ORDER_CONTACT_PHONE=? WHERE GS_ORG_ID = ? and GS_DELIVER_GOODS_ADDR_ID = ?";
        Object[] tObject = new Object[5];
        tObject[0] = aGSOrderDeptInfoBean.getGs_deliver_goods_addr();
        tObject[1] = aGSOrderDeptInfoBean.getGs_order_contact_name();
        tObject[2] = aGSOrderDeptInfoBean.getGs_order_contact_phone();
        tObject[3] = aGSOrderDeptInfoBean.getGs_org_id();
        tObject[4] = aGSOrderDeptInfoBean.getGs_deliver_goods_addr_id();
        
       

        boolean tResult = false;
        try {
            tResult = executeUpdate(tSql, tObject);
        } catch (Exception ex) {
            GSLogWriter.error(this, ex.getMessage());
            throw new GSException(ex.getMessage());
        }

        return tResult;
    }
    
    /**
     * 在订货单位常用联系方式表GS_ORDER_DEPT_INFO中删除该订货单位编号记录
     * 
     * @param GS_ORG_ID 订货单位编号
     * 
     * return boolean
     *         
     */
    public boolean delete(String aOrgId) throws GSException {
        String tSql = "delete from GS_ORDER_DEPT_INFO where GS_ORG_ID=? ";
        Object[] tObject = { aOrgId};
        boolean tResult = false;
        try {
            tResult=executeUpdate(tSql, tObject);
        } catch (Exception ex) {
            GSLogWriter.error(this, ex.getMessage());
            throw new GSException(ex.getMessage());
        }

        return tResult;
    }
    
    /**
     * 在订货单位常用联系方式表GS_ORDER_DEPT_INFO中删除一笔记录
     * 
     * @param aOrgId 订货单位编号
     * @param aAddrId 送货地址编号
     * 
     * return boolean
     *         
     */
    public boolean deleteOne(String aOrgId,String aAddrId) throws GSException {
        String tSql = "delete from GS_ORDER_DEPT_INFO where GS_ORG_ID=? and GS_DELIVER_GOODS_ADDR_ID=?";
        Object[] tObject = { aOrgId,aAddrId };
        boolean tResult = false;
        try {
            tResult=executeUpdate(tSql, tObject);
        } catch (Exception ex) {
            GSLogWriter.error(this, ex.getMessage());
            throw new GSException(ex.getMessage());
        }

        return tResult;
    }
    /**
     * 查询操作员可操作的部门所有的送货地址
     * @param aOperId           操作员编码
     * @return
     * @throws GSException
     */
    public ArrayList queryOrderDeptAddrByOper(String aOperId) throws GSException{
        ArrayList tList = new ArrayList();
        GSOrderDeptInfoBean tGSOrgVO = new GSOrderDeptInfoBean();
        Object[] tObj = {aOperId};
        String tSql = "select * from GS_ORDER_DEPT_INFO a,GS_OPERATOR b,GS_ORGANIZATION c " + 
                      "where a.GS_ORG_ID = c.GS_ORG_ID " +
                      "and (c.GS_ORG_ID = b.GS_OPER_ORG_ID or (b.GS_OPER_ORG_ID=c.GS_UPPERORG_ID and c.GS_ORG_ID = a.GS_ORG_ID)) " +
                      "and b.GS_OPER_ID=?";
        try{
            tList = this.executeQuery(tGSOrgVO,tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tList;
    }
    /**
     * 获取订货部门所有的订货地址
     * @param aOrgList          订货部门
     * @return
     * @throws GSException
     */
    public ArrayList getDeliverGoodsAddrOfAllOrderDept(ArrayList aOrgList) throws GSException{
        GSOrderDeptInfoDAO tOrderDAO = new GSOrderDeptInfoDAO();
        ArrayList tList = new ArrayList();
        Connection tConn = null;
        try{
            tConn = tOrderDAO.getConnetion();
            for(int i=0; i<aOrgList.size(); i++){
                GSOrganizationBean tOrgBean = (GSOrganizationBean)aOrgList.get(i);
                ArrayList tRs = tOrderDAO.queryOrderDeptInfoByOrgId(tConn,tOrgBean.getGs_org_id());
                tList.addAll(tRs);
            }
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E10063,GSException.E10063_MSG);
        }finally{
            close(tConn);
        }
        return tList;
    }
    /**
     * 查询组织及送货地址信息
     * @param aOrgId            组织编码
     * @param aConn             DB连接
     * @return
     * @throws GSException
     */
    public ArrayList queryOrderDeptInfoOfOperOrg(String aOrgId,Connection aConn) throws GSException{
        ArrayList tList = new ArrayList();
        String tSql = "select A.GS_ORG_ID as P1,A.GS_ORG_NAME as P2,B.GS_DELIVER_GOODS_ADDR_ID as P3,B.GS_DELIVER_GOODS_ADDR as P4" +
                " from GS_ORGANIZATION A,GS_ORDER_DEPT_INFO B where A.GS_ORG_ID=? and A.GS_ORG_ID=B.GS_ORG_ID" +
                " and SUBSTR(A.GS_ORG_ID,0,2)<>'00' ";
        Object[] tObj = {aOrgId};
        try{
           tList = this.executeQuery(aConn,new GSOrderDeptInfoVO(),tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tList;
    }
    /**
     * 查询巴士物资级订货单位信息
     * @param aOrgId
     * @param aConn
     * @return
     * @throws GSException
     */
    public ArrayList queryOrderDeptInfoOfBusOperOrg(String aOrgId,Connection aConn) throws GSException{
        ArrayList tList = new ArrayList();
        String tSql = "select A.GS_ORG_ID as P1,A.GS_ORG_NAME as P2,B.GS_DELIVER_GOODS_ADDR_ID as P3,B.GS_DELIVER_GOODS_ADDR as P4" +
                " from GS_ORGANIZATION A,GS_ORDER_DEPT_INFO B where A.GS_ORG_ID=? and A.GS_ORG_ID=B.GS_ORG_ID order by A.GS_ORG_ID";
        Object[] tObj = {aOrgId};
        try{
           tList = this.executeQuery(aConn,new GSOrderDeptInfoVO(),tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tList;
    }

    /**
     * 查询某一组织的下级组织（非巴士物资部门）信息及组织的送货地址等信息
     * @param aConn             DB连接
     * @param orgId             组织编码
     * @return
     * @throws GSException
     */
    public ArrayList queryOrderDeptInfoOfOperSubOrg(Connection aConn,String orgId) throws GSException {
        ArrayList tList = null;
        GSOrderDeptInfoVO tGSOrderDeptBean = null;

        //机构编号非法
        if(orgId == null || orgId.length()!=4)
            throw new GSException(GSException.E20032,GSException.E20032_MSG);

        //读取自己和下属机构
        String tSql = "select A.GS_ORG_ID as P1,A.GS_ORG_NAME as P2,B.GS_DELIVER_GOODS_ADDR_ID as P3,B.GS_DELIVER_GOODS_ADDR as P4" +
                " from GS_Organization A,GS_ORDER_DEPT_INFO B where A.GS_UPPERORG_ID = ? and A.GS_ORG_ID = B.GS_ORG_ID " +
                " and SUBSTR(A.GS_ORG_ID,0,2)<>'00' order by A.GS_ORG_NAME";
        Object[] tObj = {orgId};
        try{
            tList = this.executeQuery(aConn,new GSOrderDeptInfoVO(),tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        if(tList.size() > 0){
            for(int i=0;i<tList.size();i++){
                tGSOrderDeptBean = (GSOrderDeptInfoVO)tList.get(i);
                ArrayList tList2 = queryOrderDeptInfoOfOperSubOrg(aConn,tGSOrderDeptBean.orgId);
                for(int j=0;j<tList2.size();j++)
                    tList.add(tList2.get(j));
            }
        }
        return tList;
    }
    /**
     * 查询巴士物资下属所有运营公司和修理厂订货单位及送货地址信息
     * @param aConn                DB连接
     * @return
     * @throws GSException
     */
    public ArrayList queryAllOrderDeptInfoOfSubOrg(Connection aConn) throws GSException{
        ArrayList tList = null;
        String tSql = "select A.GS_ORG_ID as P1,A.GS_ORG_NAME as P2,B.GS_DELIVER_GOODS_ADDR_ID as P3,B.GS_DELIVER_GOODS_ADDR as P4" +
                        " from GS_ORGANIZATION A,GS_ORDER_DEPT_INFO B where A.GS_ORG_ID=B.GS_ORG_ID and SUBSTR(A.GS_ORG_ID,0,2) <>'00'" +
                        " order by A.GS_ORG_NAME";
        Object tObj[] = {};
        try{
            tList = executeQuery(aConn,new GSOrderDeptInfoVO(),tSql,tObj);
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tList;
    }
}
